package com.example.coursedesign.ui.dashboard.dao;

import android.util.Log;

import com.example.coursedesign.ui.dashboard.entity.Science;
import com.example.coursedesign.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ScienceDao {
    private static final String TAG = "mysql-coursedesign-ScienceDao";

    // 获取所有科普文章
    public List<Science> getAllSciences() {
        List<Science> scienceList = new ArrayList<>();
        Connection connection = JDBCUtils.getConn();
        try {
            if (connection != null) {
                String sql = "SELECT * FROM science";
                PreparedStatement ps = connection.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String title = rs.getString("title");
                    String category = rs.getString("category");
                    String publish_time = rs.getString("publish_time");
                    int read_count = rs.getInt("read_count");
                    String content = rs.getString("content");
                    String image = rs.getString("image"); // 假设image是以Base64编码的字符串形式存储

                    // 创建一个新的Science对象并添加到列表中
                    Science science = new Science(id, title, category, publish_time, read_count, content, image);
                    scienceList.add(science);
                }
                rs.close();
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Log.d(TAG, "异常getAllSciences: " + e.getMessage());
        } finally {
            JDBCUtils.closeConnection(connection);
        }
        return scienceList;
    }

    // 根据类别获取科普文章
    public List<Science> getSciencesByCategory(String category) {
        List<Science> scienceList = new ArrayList<>();
        Connection connection = JDBCUtils.getConn();
        try {
            if (connection != null) {
                String sql = "SELECT * FROM science WHERE category = ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setString(1, category);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String title = rs.getString("title");
                    String cat = rs.getString("category");
                    String publish_time = rs.getString("publish_time");
                    int read_count = rs.getInt("read_count");
                    String content = rs.getString("content");
                    String image = rs.getString("image");

                    // 创建一个新的Science对象并添加到列表中
                    Science science = new Science(id, title, cat, publish_time, read_count, content, image);
                    scienceList.add(science);
                }
                rs.close();
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Log.d(TAG, "异常getSciencesByCategory: " + e.getMessage());
        } finally {
            JDBCUtils.closeConnection(connection);
        }
        return scienceList;
    }

    // 搜索科普文章
    public List<Science> searchSciences(String query) {
        List<Science> scienceList = new ArrayList<>();
        Connection connection = JDBCUtils.getConn();
        try {
            if (connection != null) {
                String sql = "SELECT * FROM science WHERE title LIKE ? OR content LIKE ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setString(1, "%" + query + "%");
                ps.setString(2, "%" + query + "%");
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String title = rs.getString("title");
                    String category = rs.getString("category");
                    String publish_time = rs.getString("publish_time");
                    int read_count = rs.getInt("read_count");
                    String content = rs.getString("content");
                    String image = rs.getString("image");

                    // 创建一个新的Science对象并添加到列表中
                    Science science = new Science(id, title, category, publish_time, read_count, content, image);
                    scienceList.add(science);
                }
                rs.close();
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Log.d(TAG, "异常searchSciences: " + e.getMessage());
        } finally {
            JDBCUtils.closeConnection(connection);
        }
        return scienceList;
    }

    // 获取特定ID的科普文章
    public Science getScienceById(int id) {
        Science science = null;
        Connection connection = JDBCUtils.getConn();
        try {
            if (connection != null) {
                String sql = "SELECT * FROM science WHERE id = ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setInt(1, id);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    String title = rs.getString("title");
                    String category = rs.getString("category");
                    String publish_time = rs.getString("publish_time");
                    int read_count = rs.getInt("read_count");
                    String content = rs.getString("content");
                    String image = rs.getString("image");

                    // 创建一个新的Science对象
                    science = new Science(id, title, category, publish_time, read_count, content, image);
                }
                rs.close();
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Log.d(TAG, "异常getScienceById: " + e.getMessage());
        } finally {
            JDBCUtils.closeConnection(connection);
        }
        return science;
    }

    /**
     * 更新指定ID的科普文章的阅读量
     *
     * @param id          文章ID
     * @param newReadCount 新的阅读量
     * @return 如果更新成功返回true，否则返回false
     */
    public boolean updateReadCount(int id, int newReadCount) {
        Connection connection = JDBCUtils.getConn();
        try {
            if (connection != null) {
                String sql = "UPDATE science SET read_count = ? WHERE id = ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setInt(1, newReadCount);
                ps.setInt(2, id);
                int rowsAffected = ps.executeUpdate();
                ps.close();
                return rowsAffected > 0; // 返回是否成功更新
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Log.d(TAG, "异常updateReadCount: " + e.getMessage());
        } finally {
            JDBCUtils.closeConnection(connection);
        }
        return false;
    }


}